SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[SP_TMDOCU_PROV_I05]
/*---------------------------------------------------------*/  
/*---Empresa              : OFISIS S.A.                  --*/  
/*---Cliente              : OFISIS S.A.              	--*/  
/*---Sistema              : Tesoreria                    --*/  
/*---M«dulo               : Empresa                      --*/  
/*---Programa             : Inserci½n tmdocu_prov        --*/  
/*---Script               : ttproc_i.sql                 --*/  
/*---Nombre SP            : SP_TMDOCU_PROV_I05           --*/  
/*---Desarrollado por     : E.G.T                        --*/  
/*---Fecha Creaci«n       : 21/09/2005                   --*/  
/*---Base Datos           : Microsoft Sql Server         --*/  
/*---Versi«n              : 8.0                          --*/  
/*---Invoca a SP          :                              --*/  
/*---------------------------------------------------------*/  
/*-------------------------------------------------------*/
/*---Modificado 1 por     : Carlos Marquez Escobar       --*/
/*---Fecha Modificaci÷n   : 21/09/2005                 	 --*/ 
/*---Detalle Modificaci÷n : Conf Tabla Temporal		     --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Modificado 2 por     : Carlos Marquez Escobar       --*/
/*---Fecha Modificaci÷n   : 24/10/2005                 	 --*/ 
/*---Detalle Modificaci÷n : No incluye lineas Nulas	     --*/
/*-------------------------------------------------------*/
/*---------------------------------------------------------*/  
/*---Comentarios                                         --*/  
/*---                                                    --*/  
/*---                                                    --*/  
/*---------------------------------------------------------*/  
/*---------------------------------------------------------*/  
/*---Drop Proc SP_TMDOCU_PROV_I05                        --*/  
/*---Grant Execute on SP_TMDOCU_PROV_I05 to public       --*/
/*---

	delete TMDOCU_PROV
 	delete TDDISP_CCOS
	Execute SP_TMDOCU_PROV_I05 'Luz_Sur.XLS'
														--*/
/*---------------------------------------------------------*/  
@ISNO_ARCH  TD_VC_100
	As
Declare
@VSDE_SELE  VARCHAR(8000),
@VSRU_EXCE  VARCHAR(15)  


BEGIN TRAN SP_TMDOCU_PROV_I05

select @VSRU_EXCE = LTrim(RTrim(@ISNO_ARCH))

select @VSRU_EXCE = SubString(@VSRU_EXCE,1,Len(@VSRU_EXCE) - 4 )

/* PRUEBA DE EJECUCION
Execute
(
"SELECT	CO_EMPR, CO_PROV, CO_TIPO_DOCU, (Replicate('0', 15 - Len (NU_DOCU_PROV)) + NU_DOCU_PROV) NU_DOCU_PROV, '998', FE_EMIS, FE_VENC, FE_REGI_COMP, FE_PROG_PAGO, FA_TIPO_CAMB, CO_MONE, IM_BRUT_AFEC, IM_BRUT_INAF, CO_IMP1, PC_IMP1, IM_IMP1, CO_IMP2, "+
"IsNull(PC_IMP2,0.0), IM_IMP2, CO_IMP3, IsNull(PC_IMP3,0.0), IM_IMP3, IM_TOTA, IM_PAGA, DE_OBSE, CO_COND_PAGO, CO_UNID_CONC, 'ACT',	'TRANSFER', GetDate(), 'TRANSFER', GetDate() " +
" FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=D:\Recibos Energia\"
+@VSRU_EXCE+
"\"
+@ISNO_ARCH+
"', 'Select * from [CABECERA$]')"+
" WHERE CO_EMPR IS NOT NULL "
)
*/


Execute
("INSERT INTO TMDOCU_PROV
(	CO_EMPR, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV, CO_TIPO_OPER, FE_EMIS, FE_VENC, FE_REGI_COMP, FE_PROG_PAGO, FA_TIPO_CAMB, CO_MONE, IM_BRUT_AFEC, IM_BRUT_INAF, CO_IMP1, PC_IMP1, IM_IMP1, CO_IMP2, PC_IMP2, IM_IMP2, CO_IMP3, PC_IMP3, IM_IMP3, IM_TOTA, IM_PAG
A, DE_OBSE, CO_COND_PAGO, CO_UNID_CONC,
	CO_ESTA_DOCU,
	CO_USUA_CREA, FE_USUA_CREA, CO_USUA_MODI, FE_USUA_MODI ) "+
"SELECT	CO_EMPR, CO_PROV, CO_TIPO_DOCU, (Replicate('0', 15 - Len (NU_DOCU_PROV)) + NU_DOCU_PROV) NU_DOCU_PROV, '998', FE_EMIS, FE_VENC, FE_REGI_COMP, FE_PROG_PAGO, FA_TIPO_CAMB, CO_MONE, IM_BRUT_AFEC, IM_BRUT_INAF, CO_IMP1, PC_IMP1, IM_IMP1, CO_IMP2, "+
"IsNull(PC_IMP2,0.0), IM_IMP2, CO_I
MP3, IsNull(PC_IMP3,0.0), IM_IMP3, IM_TOTA, IM_PAGA, DE_OBSE, CO_COND_PAGO, CO_UNID_CONC, 'ACT',	'TRANSFER', GetDate(), 'TRANSFER', GetDate() " +
" FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=D:\Recibos Energia\"
+@VSRU_EXCE+
"\"
+@ISNO_ARCH+
"', 'Select * from [CABECERA$]') " +
" WHERE CO_EMPR IS NOT NULL "
)


IF (@@ERROR != 0)  
BEGIN  
   RAISERROR 20001 'ERROR DE INSERCION TMDOCU'  
   ROLLBACK TRAN  SP_TMDOCU_PROV_I05
   RETURN  
END  

Execute
("INSERT INTO TDDISP_CCOS
(CO_EMPR, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV, NU_CORR, TI_AUXI_EMPR, CO_CENT_COST, CO_CNTA_EMPR, CO_ORDE_SERV, TI_DOCU, NU_DOCU, FE_VENC, ST_DIST, PO_DIST, IM_DIST, DE_OBSE,
	CO_USUA_CREA, FE_USUA_CREA, CO_USUA_MODI, FE_USUA_MODI ) " +
"SELECT	CO_EMPR, CO_PROV, CO_TIPO_DOCU, (Replicate('0', 15 - Len (NU_DOCU_PROV)) + NU_DOCU_PROV) NU_DOCU_PROV, NU_CORR, TI_AUXI_EMPR, CO_CENT_COST, CO_CNTA_EMPR, CO_ORDE_SERV, TI_DOCU, NU_DOCU, FE_VENC, ST_DIST, PO_DIST, IM_DIST, DE_OBSE, " +
" 'TRANSFER', GetDate(), 'TRANSFER', GetDate()" +
" FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=D:\Recibos Energia\"
+@VSRU_EXCE+
"\"
+@ISNO_ARCH+
"', 'Select * from [DETALLE$]')"+
" WHERE CO_EMPR IS NOT NULL "
)

IF (@@ERROR != 0)  
BEGIN  
   RAISERROR 20001 'ERROR DE INSERCION TDDISP'  
   ROLLBACK TRAN  SP_TMDOCU_PROV_I05
   RETURN  
END



COMMIT TRAN SP_TMDOCU_PROV_I05

/*--------------------------- Fin -------------------------------- */


GO
